
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimCustomer_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimCustomer_Insert]
GO


create procedure dbo.p_DimCustomer_Insert (
	@CustomerIdentifier varchar (30),
	@FullName nvarchar (150),
	@NamePrefix nvarchar (50),
	@GivenName nvarchar (50),
	@FamilyName nvarchar (50),
	@MiddleName nvarchar (50),
	@NameSuffix nvarchar (20),
	@Initials varchar (20),
	@Nickname nvarchar (50),
	@MadienName nvarchar (50),
	@MarriedName nvarchar (50),
	@ProfessionalTtitle nvarchar (150),
	@AcademicTitle nvarchar (150),
	@EmailAddress varchar (256),
	@EmailAddressDomain varchar (150),
	@DateOfBirth datetime,
	@Age int,
	@Gender nvarchar (20),
	@Locality nvarchar (150),
	@PhoneNumber varchar (20),
	@Nationality nvarchar (50),
	@SocialSecurityNumber varchar (30),
	@Relationship nvarchar (30),
	@CustomerKey int output)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimCustomerInsert
        else
	    save transaction txnDimCustomerInsert

	set @Error = @@error
	if (@Error <> 0) goto cleanup		

	insert into dbo.DimCustomer (CustomerIdentifier,
				     FullName,
				     NamePrefix,
				     GivenName,
				     FamilyName,
				     MiddleName,
				     NameSuffix,
				     Initials,
				     Nickname,
				     MadienName,
				     MarriedName,
				     ProfessionalTitle,
				     AcademicTitle,
				     EmailAddress,
				     EmailAddressDomain,
				     DateOfBirth,
				     Age,
				     Gender,
				     Locality,
				     PhoneNumber,
				     Nationality,
				     SocialSecurityNumber,
				     Relationship)
	  values (@CustomerIdentifier,
		  @FullName,
		  @NamePrefix,
		  @GivenName,
		  @FamilyName,
		  @MiddleName,
		  @NameSuffix,
		  @Initials,
		  @Nickname,
		  @MadienName,
		  @MarriedName,
		  @ProfessionalTitle,
		  @AcademicTitle,
		  @EmailAddress,
		  @EmailAddressDomain,
		  @DateOfBirth,
		  @Age,
		  @Gender,
		  @Locality,
		  @PhoneNumber,
		  @Nationality,
		  @SocialSecurityNumber,
		  @Relationship)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -1
	if (@Error <> 0) goto cleanup

	set @CustomerKey = scope_identity()

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimCustomerInsert
		else
		if (@inTranCount = 0) commit transaction txnDimCustomerInsert

	end

	return @Error

end
GO

grant execute on dbo.p_DimCustomer_Insert  to [db_datawriter]
go
